package SqlDB;

import java.sql.*;

import com.sun.org.apache.xpath.internal.functions.Function;

import java.sql.*;

public class Main {
	
	private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	
	  // 连接路径
	private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=yngsxy";
	
	private static Statement stmt=null; 
	
	private static Connection conn = null;
	
	private static ResultSet rs=null;
	
	private static final String user= "sa";
	
	private static final String password="123456";
	
	public void Select1() throws SQLException {
		
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	
			try {
				conn = (Connection) DriverManager.getConnection(URL,user,password);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			stmt.close();
			conn.close();
	
			//Select查询方法	
			
			try {
				   stmt=(Statement) ((java.sql.Connection) conn).createStatement();
				   rs = ((java.sql.Statement) stmt).executeQuery("SELECT * FROM Users");
				   
				   while(rs.next()){
					   int i = 1;
					   String id=rs.getString(i++);
					   String name=rs.getString(i++);
					   String pwd=rs.getString(i++);
					   int role=rs.getInt(i++);
					   String depict=rs.getString(i++);
					   
					   System.out.println("编号："+id+"姓名："+name+"密码："+pwd+"角色："+role+"说明："+depict);

				   }
				   
				   
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			
			//insert插入方法			
			try {
				   stmt=(Statement) ((java.sql.Connection) conn).createStatement();
				   String sql="INSERT INTO Users (LoginID, Name,Pwd,Role,Depict ) VALUES ('1004','balabala','123456','2','教师')";
				   rs = ((java.sql.Statement) stmt).executeQuery(sql);
				   
				   while(rs.next()){
					   int i = 1;
					   String id=rs.getString(i++);
					   String name=rs.getString(i++);
					   String pwd=rs.getString(i++);
					   int role=rs.getInt(i++);
					   String depict=rs.getString(i++);
					   
					   System.out.println("编号："+id+"姓名："+name+"密码："+pwd+"角色："+role+"说明："+depict);

					   System.out.println("插入数据成功");

				   }
				   
				   
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			
			//Update修改方法			
			try {
				   stmt=(Statement) ((java.sql.Connection) conn).createStatement();
				   String sql="UPDATE Users SET Name = '哒哒' WHERE LoginID = '1004'";
				   rs = ((java.sql.Statement) stmt).executeQuery(sql);
				   
				   while(rs.next()){
					   int i = 1;
					   String id=rs.getString(i++);
					   String name=rs.getString(i++);
					   String pwd=rs.getString(i++);
					   int role=rs.getInt(i++);
					   String depict=rs.getString(i++);
					   
					   System.out.println("编号："+id+"姓名："+name+"密码："+pwd+"角色："+role+"说明："+depict);

					   System.out.println("修改数据成功");

				   }
				   
				   
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			
			//Delete删除方法			
			try {
				   stmt=(Statement) ((java.sql.Connection) conn).createStatement();
				   String sql="DELETE FROM Users WHERE LoginID='1004'";
				   rs = ((java.sql.Statement) stmt).executeQuery(sql);
				   
				   while(rs.next()){
					   int i = 1;
					   String id=rs.getString(i++);
					   String name=rs.getString(i++);
					   String pwd=rs.getString(i++);
					   int role=rs.getInt(i++);
					   String depict=rs.getString(i++);
					   
					   System.out.println("编号："+id+"姓名："+name+"密码："+pwd+"角色："+role+"说明："+depict);

					   System.out.println("删除数据成功");

				   }
				   
				   
			} catch (SQLException e) {
				e.printStackTrace();
			}
			

	}
	
	public static void main(String [] args)
	 {
	  /*Main main = new Main();
	  main.Select1();*/
	 }
}

